import sys
import os
import logging
import re
import json
from typing import Dict, List, Any, Optional, Tuple

# 添加项目根路径到 sys.path
sys.path.append(os.path.join(os.path.dirname(os.path.abspath(__file__)), '..', '..'))

# 导入 MySQLUtil
from shared.utils.MySQLUtil import MySQLUtil

# 设置日志
logger = logging.getLogger(__name__)

def get_指定学历的前十基础能力水平值(
    project_id: int,
    questionnaire_ids: List[int],
    product_code: Optional[str] = None,
    project_code: Optional[str] = None,
    region_code: Optional[str] = None,
    education: Optional[str] = None,
    top_n: int = 10
) -> Dict[str, Any]:
    """
    指定学历的前十基础能力水平值 - 指标计算函数
    
    ## 指标说明
    该指标用于计算指定学历（本科/专科/硕士/博士）的学生群体中，基础能力水平值排名前十的能力项。
    计算逻辑：
    1. 先统计多选题各选项的答题人数
    2. 筛选出答题人数最多的前N个选项
    3. 对每个选项关联的单选题计算水平值（选项占比×权重）
    4. 返回前N个选项的水平值
    
    ## Args
        project_id (int): 项目ID，用于查询项目配置信息
        questionnaire_ids (List[int]): 问卷ID集合，用于确定数据范围
        product_code (Optional[str]): 产品编码
        project_code (Optional[str]): 项目编码
        region_code (Optional[str]): 区域编码
        education (Optional[str]): 学历筛选条件，可选值：本科毕业生/专科毕业生/硕士研究生/博士研究生
        top_n (int): 返回前N个能力项，默认为10
    
    ## 示例
    ### 输入
    ```json
    {
        "project_id": 5895,
        "questionnaire_ids": [11158, 11159],
        "education": "博士研究生"
    }
    ```
    
    ### 输出
    ```json
    {
        "success": true,
        "message": "ok", 
        "code": 0,
        "result": [
            {
                "wdKeyNameList": [],
                "calcVals": [
                    {
                        "val": [
                            {
                                "key": "default",
                                "val": "记忆能力和严谨负责的水平相对较高，均值分别为4.00分和4.00分"
                            }
                        ]
                    }
                ]
            }
        ]
    }
    ```
    """
    logger.info(f"开始计算指标: 指定学历的前十基础能力水平值, 项目ID: {project_id}")
    
    try:
        db = MySQLUtil()  

        # 1. 验证和处理学历参数
        valid_educations = ['本科毕业生', '专科毕业生', '硕士研究生', '博士研究生']
        # 处理空字符串的情况
        if education == '':
            education = None
        if education and education not in valid_educations:
            raise ValueError(f"学历参数必须为以下值之一: {valid_educations}")
        
        logger.info(f"学历筛选条件: {education if education else '不限制学历'}")

        # 2. 查询项目配置信息
        project_sql = """
        SELECT client_code, item_year, dy_target_items, split_tb_paper 
        FROM client_item 
        WHERE id = %s
        """
        project_info = db.fetchone(project_sql, (project_id,))
        if not project_info:
            raise ValueError(f"未找到项目ID={project_id}的配置信息")

        client_code = project_info['client_code']
        item_year = project_info['item_year']
        split_tb_paper = project_info['split_tb_paper']
        
        logger.info(f"项目配置: client_code={client_code}, item_year={item_year}, split_tb_paper={split_tb_paper}")

        # 3. 计算 shard_tb_key
        shard_tb_key = re.sub(r'^[A-Za-z]*0*', '', client_code)
        logger.info(f"计算得到 shard_tb_key: {shard_tb_key}")

        # 4. 查询问卷信息
        questionnaire_sql = f"""
        SELECT id, dy_target 
        FROM wt_template_customer 
        WHERE id IN ({','.join(['%s'] * len(questionnaire_ids))})
        """
        questionnaires = db.fetchall(questionnaire_sql, tuple(questionnaire_ids))
        if not questionnaires:
            raise ValueError(f"未找到问卷ID集合={questionnaire_ids}的配置信息")
        
        logger.info(f"查询到问卷信息: {questionnaires}")

        # 5. 过滤GRADUATE_SHORT问卷
        valid_questionnaire_ids = [q['id'] for q in questionnaires if q['dy_target'] == 'GRADUATE_SHORT']
        if not valid_questionnaire_ids:
            raise ValueError("未找到GRADUATE_SHORT调研对象的问卷ID")
            
        logger.info(f"找到有效问卷ID: {valid_questionnaire_ids}")

        # 6. 查询多选题信息
        multi_question_sql = """
        SELECT id, wt_code, wt_obj 
        FROM wt_template_question_customer 
        WHERE cd_template_id = %s AND wt_code = 'T00000446' AND is_del = 0
        """
        multi_question = db.fetchone(multi_question_sql, (valid_questionnaire_ids[0],))
        if not multi_question:
            raise ValueError("未找到多选题T00000446的问题信息")
            
        logger.info(f"找到多选题信息: {multi_question['id']}")

        # 7. 解析多选题选项
        wt_obj = json.loads(multi_question['wt_obj'])
        options = [{
            'key': item['key'],
            'val': item['val'],
            'weight': item.get('weight', 1)
        } for item in wt_obj['itemList']]
        option_map = {opt['key']: opt for opt in options}

        # 8. 构建动态表名
        answer_table = f"re_dy_paper_answer_{split_tb_paper}"
        student_table = f"dim_client_target_baseinfo_student_{item_year}"

        # 9. 统计多选题各选项答题人数
        count_sql = f"""
        SELECT
        {', '.join([f'sum(t1.c{opt["key"]}) as "{opt["val"]}"' for opt in options])}
        FROM {answer_table} t1
        JOIN {student_table} s ON t1.target_no = s.target_no
        WHERE
          t1.cd_template_id = %s
          AND t1.wid = %s
          AND t1.ans_true = 1
          AND s.shard_tb_key = %s
          AND s.item_year = %s
          {f"AND s.education = %s" if education else ""}
        """
        params = [valid_questionnaire_ids[0], multi_question['id'], shard_tb_key, item_year]
        if education:
            params.append(education)
            
        option_counts = db.fetchone(count_sql, tuple(params))
        if not option_counts:
            raise ValueError("未找到多选题的答题数据")

        # 10. 排序并取前N个选项
        sorted_options = sorted(
            [(opt['val'], option_counts[opt['val']] or 0) for opt in options],
            key=lambda x: x[1],
            reverse=True
        )[:top_n]
        top_options = [opt[0] for opt in sorted_options]
        
        logger.info(f"答题人数前{top_n}的选项: {top_options}")

        # 11. 定义单选题映射关系（完整映射）
        single_question_map = {
            "善于观察": ["T00000498", "T00000499"],
            "记忆能力": ["T00000500", "T00000501"],
            "阅读理解": ["T00000502", "T00000517"],
            "数学运算": ["T00000518", "T00000519"],
            "逻辑思维": ["T00000520", "T00000521", "T00000522", "T00000523", "T00000524"],
            "创新思维": ["T00000525", "T00000503", "T00000526", "T00000527", "T00000528"],
            "善于倾听": ["T00000529", "T00000504"],
            "表达能力": ["T00000530", "T00000531"],
            "团队意识": ["T00000532", "T00000533", "T00000534"],
            "领导意识": ["T00000535", "T00000536"],
            "制定计划": ["T00000537", "T00000538"],
            "组织协调": ["T00000539", "T00000540", "T00000541"],
            "情绪调节": ["T00000505", "T00000542"],
            "形象管理": ["T00000506", "T00000543"],
            "主动学习": ["T00000544", "T00000545", "T00000546"],
            "严谨负责": ["T00000547", "T00000507", "T00000548", "T00000508"],
            "外向主动": ["T00000549", "T00000550", "T00000551", "T00000509"],
            "坚韧自信": ["T00000552", "T00000553", "T00000554", "T00000510"],
            "诚信助人": ["T00000555", "T00000511", "T00000556", "T00000557", "T00000512", "T00000558"],
            "勇于探索": ["T00000559", "T00000513", "T00000560", "T00000514", "T00000561", "T00000515"],
            "了解自我": ["T00000562", "T00000563"],
            "保持健康": ["T00000564", "T00000565", "T00000516"]
        }

        # 12. 计算每个选项的水平值
        result = []
        for option in top_options:
            if option not in single_question_map:
                continue
                
            total_score = 0
            question_count = 0
            
            for q_code in single_question_map[option]:
                # 查询单选题信息
                single_question_sql = """
                SELECT id, wt_code, wt_obj 
                FROM wt_template_question_customer 
                WHERE cd_template_id = %s AND wt_code = %s AND is_del = 0
                """
                single_question = db.fetchone(single_question_sql, (valid_questionnaire_ids[0], q_code))
                if not single_question:
                    continue
                    
                # 计算该题水平值（在SQL中直接计算加权平均分）
                score_sql = f"""
                SELECT
                CASE 
                    WHEN (sum(t1.c1)+sum(t1.c2)+sum(t1.c3)+sum(t1.c4)+sum(t1.c5)) = 0 THEN NULL
                    ELSE (
                        sum(t1.c1)/(sum(t1.c1)+sum(t1.c2)+sum(t1.c3)+sum(t1.c4)+sum(t1.c5)) * 5 +
                        sum(t1.c2)/(sum(t1.c1)+sum(t1.c2)+sum(t1.c3)+sum(t1.c4)+sum(t1.c5)) * 4 +
                        sum(t1.c3)/(sum(t1.c1)+sum(t1.c2)+sum(t1.c3)+sum(t1.c4)+sum(t1.c5)) * 3 +
                        sum(t1.c4)/(sum(t1.c1)+sum(t1.c2)+sum(t1.c3)+sum(t1.c4)+sum(t1.c5)) * 2 +
                        sum(t1.c5)/(sum(t1.c1)+sum(t1.c2)+sum(t1.c3)+sum(t1.c4)+sum(t1.c5)) * 1
                    )
                END as weighted_score
                FROM {answer_table} t1
                JOIN {student_table} s ON t1.target_no = s.target_no
                WHERE
                  t1.cd_template_id = %s
                  AND t1.wid = %s
                  AND t1.ans_true = 1
                  AND s.shard_tb_key = %s
                  AND s.item_year = %s
                  {f"AND s.education = %s" if education else ""}
                """
                params = [valid_questionnaire_ids[0], single_question['id'], shard_tb_key, item_year]
                if education:
                    params.append(education)
                    
                result_row = db.fetchone(score_sql, tuple(params))
                if not result_row or result_row['weighted_score'] is None:
                    logger.debug(f"问题 {q_code} (wid={single_question['id']}) 无有效数据")
                    continue
                    
                # 获取加权平均分
                score = float(result_row['weighted_score'])
                logger.debug(f"  问题 {q_code} (wid={single_question['id']}) 水平值: {score:.2f}分")
                
                total_score += score
                question_count += 1
                
            if question_count > 0:
                avg_score = round(total_score / question_count, 2)
                result.append({
                    "option": option,
                    "score": avg_score
                })
                logger.info(f"选项 '{option}' 水平值: {avg_score}分 (基于{question_count}道题)")

        # 13. 格式化返回结果
        if not result:
            message = "未计算出有效结果"
            logger.warning("所有选项都没有计算出有效的水平值")
        else:
            # 按分数排序，取前2个
            top_scores = sorted(result, key=lambda x: x['score'], reverse=True)[:2]
            if len(top_scores) == 1:
                message = f"{top_scores[0]['option']}的水平相对较高，均值为{top_scores[0]['score']}分"
            else:
                message = f"{top_scores[0]['option']}和{top_scores[1]['option']}的水平相对较高，均值分别为{top_scores[0]['score']}分和{top_scores[1]['score']}分"
            
            logger.info(f"计算结果: {len(result)}个选项有效，前2名: {[item['option'] for item in top_scores]}")

        logger.info(f"指标计算完成: {message}")
        return {
            "success": True,
            "message": "ok",
            "code": 0,
            "result": [{
                "wdKeyNameList": [],
                "calcVals": [{
                    "val": [{
                        "key": "default",
                        "val": message
                    }]
                }]
            }]
        }

    except Exception as e:
        logger.error(f"计算指标时发生错误: {str(e)}", exc_info=True)
        return {
            "success": False,
            "message": f"数据获取失败: {str(e)}",
            "code": 500,
            "error": str(e)
        }